<?php
/**
 * 生成数据库命令
 * 还在完善中
 */
namespace Yousheng\DatabaseToolbox\Console;

use Illuminate\Console\Command;
use Illuminate\Console\GeneratorCommand;
use PDO;
use PDOException;
use Symfony\Component\Console\Attribute\AsCommand;
use Yousheng\Ku\Ku;
use Illuminate\Support\Facades\DB;
use Laravel\Nova\Fields\Boolean;

#[AsCommand(name:'ku:create-database2',description:'创建数据库')]
class DatabaseCreateCommand2 extends Command
{

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $this->info("创建数据库需要管理员权限,请输入管理员权限的用户名和密码");
        $database = env('DB_DATABASE', false);
        $host = env('DB_HOST', '127.0.0.1');
        $port = env('DB_PORT', 3306);
        $username = env('DB_USERNAME', false);
        $password = env('DB_PASSWORD', false);

        if (! $database ) {
            $this->info('Skipping creation of database as env(DB_DATABASE) is empty');
            return;
        }
        if(!($host && $username && $password)){
            $this->error('please alter file .env for database configuation');
            return;
        }

        $this->info("please enter mysql root name and password for create database ".$database);
        $rootUsername = $this->ask('mysql root name');
        $rootPassword = $this->ask('root password');
        Ku::setAdminConnectioConfig([
            'driver' => 'mysql',
            'database' => $database,
            'host' => $host,
            'port' => $port,
            'username' => $rootUsername,
            'password' => $rootPassword,
        ]);
        $adminConnection = Ku::getAdminConnection();

        // 修复：添加分号以结束上一行代码
      
        $databaseExists=false;


        // 判断数据库是否存在方法一
        // try {
        //     // 尝试连接数据库
        //     DB::connection()->statement($database);
        //     $databaseExists=true;
        // } catch (\Exception $e) {
        //     // 捕获异常，判断数据库是否不存在
        //     if ($e->getCode() == 1049) {
        //         $databaseExists=false;
        //     } else {
        //         echo "发生其他错误: " . $e->getMessage();
        //     }
        // }

         // 判断数据库是否存在方法二
        //  $databaseExists = (bool)DB::select("SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME =?", [$database]);

        try {
            // 判断数据库是否存在方法三
            $pdo = $adminConnection->getPdo();

            $stmt = $pdo->query("SELECT COUNT(*) FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '$database'");
            $databaseExists =  (bool) $stmt->fetchColumn();

            if(!$databaseExists){
                $pdo->exec(sprintf(
                    // 'CREATE DATABASE IF NOT EXISTS %s CHARACTER SET %s COLLATE %s;',
                    'CREATE DATABASE %s CHARACTER SET %s COLLATE %s;',
                    $database,
                    env('DB_CHARSET')??'utf8mb4',
                    env('DB_COLLATION')??'utf8mb4_unicode_ci'
                ));
                $this->info(sprintf('Successfully created %s database', $database));
            }else{
                $this->warn(sprintf('database %s exists', $database));
            }

            /*  @todo mysql 版本检测
                $version = $pdo->query('select version()')->fetchColumn();
                (float)$version = mb_substr($version, 0, 6);
                if ($version < '5.6.10') {
                    $this->info('不支持5.6.10下的版本');
                    return ;
                } 
            */
            if($rootUsername !== $username /* && $this->option('user')*/){
                // 先判断mysql用户是否存在
                $sql = "SELECT 1 FROM mysql.user WHERE user = :username";
                $stmt = $pdo->prepare($sql);
                $stmt->bindParam(':username', $username, PDO::PARAM_STR);
                $stmt->execute();
                if ($stmt->rowCount() > 0) {
                    $this->warn(sprintf('user %s exists', $username));
                } else {
                    $pdo->exec("CREATE USER IF NOT EXISTS '$username'@'%' IDENTIFIED BY '$password'");
                    $pdo->exec("GRANT ALL ON `$database`.* TO '$username'@'%'");
                    $this->info(sprintf('Successfully created  user %s', $username));
                }
            }
        } catch (PDOException $exception) {
            $this->error(sprintf('Failed to create %s database, %s', $database, $exception->getMessage()));
        }
    }
}